package org.yangjie.poi;

import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Objects;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;

/**
 * Excel文件处理工具
 * 支持自动识别处理xls和xlsx文件
 * @author YangJie [2018年11月16日]
 */
public class ExcelUtil {
	
	
	/**
	 * 读取
	 * 自动识别文件类型
	 * @param inputStream 文件输入流
	 * @return
	 * @throws Exception
	 */
	public static List<List<List<Object>>> read(InputStream inputStream) throws Exception {
		List<List<List<Object>>> list = new ArrayList<>();
		Workbook workbook = WorkbookFactory.create(inputStream);
		for(int i=0; i<workbook.getNumberOfSheets(); i++) {
			list.add(readSheet(workbook.getSheetAt(i)));
		}
		workbook.close();
		return list;
	}
	
	/**
	 * 读取
	 * 自动识别文件类型
	 * @param inputStream 文件输入流
	 * @param sheetIndex 指定sheet下标
	 * @return
	 * @throws Exception
	 */
	public static List<List<Object>> read(InputStream inputStream, int sheetIndex) throws Exception {
		Workbook workbook = WorkbookFactory.create(inputStream);
		List<List<Object>> list = readSheet(workbook.getSheetAt(sheetIndex));
		workbook.close();
		return list;
	}
	
	/**
	 * 读取 sheet
	 * @param sheet
	 * @return
	 */
	private static List<List<Object>> readSheet(Sheet sheet) {
		List<List<Object>> sheetList = new ArrayList<>(sheet.getLastRowNum());
		for(int i=sheet.getFirstRowNum(); i<=sheet.getLastRowNum(); i++) {
			sheetList.add(readRow(sheet.getRow(i)));
		}
		return sheetList;
	}
	
	/**
	 * 读取 行
	 * @param row
	 * @return
	 */
	private static List<Object> readRow(Row row) {
		if(Objects.isNull(row) || row.getLastCellNum()<=0) {
			return Collections.emptyList();
		}
		List<Object> rowList = new ArrayList<>(row.getLastCellNum());
		for(int i=row.getFirstCellNum(); i<=row.getLastCellNum(); i++) {
			rowList.add(readCell(row.getCell(i)));
		}
		return rowList;
	}
	
	/**
	 * 读取 单元格
	 * @param cell
	 * @return
	 */
	private static Object readCell(Cell cell) {
		if(Objects.isNull(cell)) {
			return "";
		}
		switch (cell.getCellTypeEnum()) {
		case STRING:
			return cell.getStringCellValue();
		case NUMERIC:
			// 避免单元格内容为纯数字的情况读出.0
			return Double.valueOf(cell.getNumericCellValue()).longValue();
		case BOOLEAN:
			return cell.getBooleanCellValue();
		default:
			return "";
		}
	}
	
	/**
	 * 读取图片
	 * @param inputStream
	 * @return
	 * @throws Exception
	 */
	public static List<ReadImage> readImage(InputStream inputStream) throws Exception {
		List<ReadImage> List = new ArrayList<>();
		Workbook workbook = WorkbookFactory.create(inputStream);
		List<? extends PictureData> picList = workbook.getAllPictures();
		for(PictureData pic : picList) {
			ReadImage readImage = new ReadImage();
			readImage.setType(pic.getMimeType());
			readImage.setSuffix(pic.suggestFileExtension());
			readImage.setBytes(pic.getData());
			List.add(readImage);
		}
		return List;
	}
	
	
	/**
	 * 创建文件
	 * 只写入1行
	 * @param outputStream
	 * @param List
	 * @return
	 * @throws Exception
	 */
	public static void create(OutputStream outputStream, Object... objs) throws Exception {
		create(outputStream, Arrays.asList(Arrays.asList(objs)));
	}
	
	/**
	 * 创建文件
	 * 只写入n行
	 * @param outputStream
	 * @param List
	 * @return
	 * @throws Exception
	 */
	@SafeVarargs
	public static void create(OutputStream outputStream, List<Object>... rows) throws Exception {
		create(outputStream, Arrays.asList(rows));
	}
	
	/**
	 * 创建文件
	 * 默认创建xlsx
	 * 默认数据写入第一个命名为first的sheet
	 * @param outputStream
	 * @param sheetlist
	 * @return
	 * @throws Exception
	 */
	public static void create(OutputStream outputStream, List<List<Object>> sheetlist) throws Exception {
		create(outputStream, sheetlist, "first");
	}
	
	/**
	 * 创建文件
	 * 默认创建xlsx
	 * @param outputStream
	 * @param sheetlist
	 * @param sheetName 指定sheet名称
	 * @return
	 * @throws Exception
	 */
	public static void create(OutputStream outputStream, List<List<Object>> sheetlist, String sheetName) throws Exception {
		Workbook workbook = new SXSSFWorkbook();
		createSheet(workbook.createSheet(sheetName), sheetlist);
		workbook.write(outputStream);
		workbook.close();
	}
	
	/**
	 * 创建文件
	 * 默认创建xlsx
	 * 一次性写入多页 
	 * @param outputStream
	 * @param dataMap key为sheet名称
	 * @return
	 * @throws Exception
	 */
	public static void create(OutputStream outputStream, Map<String, List<List<Object>>> dataMap) throws Exception {
		Workbook workbook = new SXSSFWorkbook();
		for(Entry<String, List<List<Object>>> entry : dataMap.entrySet()) {
			createSheet(workbook.createSheet(entry.getKey()), entry.getValue());
		}
		workbook.write(outputStream);
		workbook.close();
	}
	
	/**
	 * 创建 sheet
	 * @param sheet
	 * @param sheetList
	 * @return
	 * @throws Exception
	 */
	private static void createSheet(Sheet sheet, List<List<Object>> sheetList) throws Exception {
		if(Objects.isNull(sheetList)) {
			return;
		}
		for (int i=0; i<sheetList.size(); i++) {
			Row row = sheet.createRow(i);
			List<Object> rowlist = sheetList.get(i);
			for(int j=0; j<rowlist.size(); j++) {
				writeCell(row.createCell(j), rowlist.get(j));
			}
		}
	}
	
	/**
	 * 写入文件
	 * 向已有文件中固定位置写入
	 * 默认写入第一个sheet
	 * @param inputStream
	 * @param outputStream
	 * @param writeables
	 * @return
	 * @throws Exception
	 */
	public static void write(InputStream inputStream, OutputStream outputStream, Writeable... writeables) throws Exception {
		write(inputStream, outputStream, Arrays.asList(writeables));
	}
	
	/**
	 * 写入文件
	 * 向已有文件中固定位置写入
	 * 默认写入第一个sheet
	 * @param inputStream
	 * @param outputStream
	 * @param writeables
	 * @return
	 * @throws Exception
	 */
	public static void write(InputStream inputStream, OutputStream outputStream, List<Writeable> writeableList) throws Exception {
		write(0, inputStream, outputStream, writeableList);
	}
	
	/**
	 * 写入文件
	 * 向已有文件中固定位置写入
	 * 按指定sheet下标写入
	 * @param sheetIndex
	 * @param inputStream
	 * @param outputStream
	 * @param writeables
	 * @return
	 * @throws Exception
	 */
	public static void write(int sheetIndex, InputStream inputStream, OutputStream outputStream, List<Writeable> writeableList) throws Exception {
		Workbook workbook = WorkbookFactory.create(inputStream);
		writeSheet(workbook, workbook.getSheetAt(sheetIndex), writeableList);
		workbook.write(outputStream);
		workbook.close();
	}
	
	/**
	 * 写入文件
	 * 向已有文件中固定位置写入
	 * 按指定sheet名称写入
	 * @param sheetName
	 * @param inputStream
	 * @param outputStream
	 * @param writeables
	 * @return
	 * @throws Exception
	 */
	public static void write(String sheetName, InputStream inputStream, OutputStream outputStream, List<Writeable> writeableList) throws Exception {
		Workbook workbook = WorkbookFactory.create(inputStream);
		writeSheet(workbook, workbook.getSheet(sheetName), writeableList);
		workbook.write(outputStream);
		workbook.close();
	}
	
	/**
	 * 写入 sheet
	 * @param workbook
	 * @param sheet
	 * @param writeables
	 * @return
	 * @throws Exception
	 */
	private static void writeSheet(Workbook workbook, Sheet sheet, List<Writeable> writeableList) throws Exception {
		for(Writeable writeable : writeableList) {
			if(writeable instanceof WriteImage) { // 图片
				writeImage(workbook, sheet, (WriteImage) writeable);
			}else { // 普通数据
				writeObject(sheet, (WriteObject) writeable);
			}
		}
	}
	
	/**
	 * 写入 图片
	 * 向已有模板写入, 图片占满固定单元格
	 * @param sheet
	 * @param rowIndex 行，从0开始
	 * @param cellIndex 列，从0开始
	 * @param value
	 * @return
	 */
	private static void writeImage(Workbook workbook, Sheet sheet, WriteImage writeImage) {
		Drawing<?> drawing = sheet.createDrawingPatriarch();
		ClientAnchor anchor = new XSSFClientAnchor(writeImage.getDx1(), writeImage.getDy1(), writeImage.getDx2(), writeImage.getDy2(), (short)writeImage.getCol1(), writeImage.getRow1(), (short)writeImage.getCol2(), writeImage.getRow2());
		drawing.createPicture(anchor, workbook.addPicture(writeImage.getBytes(), Workbook.PICTURE_TYPE_JPEG)); // 此处图片类型先用固定值，亲测使用png图片可以写入成功
	}
	
	
	/**
	 * 写入 对象
	 * @param sheet
	 * @param rowIndex 行，从0开始
	 * @param cellIndex 列，从0开始
	 * @param value
	 * @return
	 */
	private static void writeObject(Sheet sheet, WriteObject writeObject) {
		int rownum = writeObject.getX();
		Row row = sheet.getRow(rownum);
		if(Objects.isNull(row)) { // 没有row就创建
			row = sheet.createRow(rownum);
		}
		int column = writeObject.getY();
		Cell cell = row.getCell(column);
		if(Objects.isNull(cell)) { // 没有cell就创建
			cell = row.createCell(column);
		}
		writeCell(cell, writeObject.getValue());
	}
	
	/**
	 * 写入 单元格
	 * @param cell
	 * @param value
	 * @return
	 */
	private static void writeCell(Cell cell, Object value) {
		if (Objects.isNull(value)) {
			return;
		}else if (value instanceof Integer) {
			cell.setCellValue((Integer)value);
		}else if (value instanceof Double) {
			cell.setCellValue((Double)value);
		}else if (value instanceof Boolean) {
			cell.setCellValue((Boolean)value);
		}else if (value instanceof Date) {
			cell.setCellValue((Date)value);
		}else if (value instanceof Calendar) {
			cell.setCellValue((Calendar)value);
		}else {
			cell.setCellValue(String.valueOf(value));
		}
	}
	
	/**
	 * 定义可写入接口
	 * @author YangJie [2018年11月22日]
	 */
	public static interface Writeable{}
	
	/**
	 * 写入数据实体
	 * @author YangJie [2018年11月16日]
	 */
	public static class WriteObject implements Writeable{
		int x; // 横坐标
		int y; // 纵坐标
		Object value; // 内容
		public int getX() {
			return x;
		}
		public WriteObject setX(int x) {
			this.x = x;
			return this;
		}
		public int getY() {
			return y;
		}
		public WriteObject setY(int y) {
			this.y = y;
			return this;
		}
		public Object getValue() {
			return value;
		}
		public WriteObject setValue(Object value) {
			this.value = value;
			return this;
		}
	}	
	
	/**
	 * 写入图片实体
	 * @author YangJie [2018年11月16日]
	 */
	public static class WriteImage implements Writeable{
		private byte[] bytes; // 文件内容
		private int row1; // 左上角所在行
		private int col1; // 左上角所在列
		private int dx1; // 左上角横轴偏移量
		private int dy1; // 左上角纵轴偏移量
		private int row2; // 右下角所在行
		private int col2; // 右下角所在列
		private int dx2; // 右下角横轴偏移量
		private int dy2; // 右下角纵轴偏移量

		public byte[] getBytes() {
			return bytes;
		}
		public WriteImage setBytes(byte[] bytes) {
			this.bytes = bytes;
			return this;
		}
		public int getRow1() {
			return row1;
		}
		public WriteImage setRow1(int row1) {
			this.row1 = row1;
			return this;
		}
		public int getCol1() {
			return col1;
		}
		public WriteImage setCol1(int col1) {
			this.col1 = col1;
			return this;
		}
		public int getDx1() {
			return dx1;
		}
		public WriteImage setDx1(int dx1) {
			this.dx1 = dx1;
			return this;
		}
		public int getDy1() {
			return dy1;
		}
		public WriteImage setDy1(int dy1) {
			this.dy1 = dy1;
			return this;
		}
		public int getRow2() {
			return row2;
		}
		public WriteImage setRow2(int row2) {
			this.row2 = row2;
			return this;
		}
		public int getCol2() {
			return col2;
		}
		public WriteImage setCol2(int col2) {
			this.col2 = col2;
			return this;
		}
		public int getDx2() {
			return dx2;
		}
		public WriteImage setDx2(int dx2) {
			this.dx2 = dx2;
			return this;
		}
		public int getDy2() {
			return dy2;
		}
		public WriteImage setDy2(int dy2) {
			this.dy2 = dy2;
			return this;
		}		
	}
	
	/**
	 * 读取图片实体
	 * @author YangJie [2018年11月16日]
	 */
	public static class ReadImage{
		private byte[] bytes; // 文件内容
		private String type; // 文件类型
		private String suffix; // 文件后缀
		
		public byte[] getBytes() {
			return bytes;
		}
		public ReadImage setBytes(byte[] bytes) {
			this.bytes = bytes;
			return this;
		}
		public String getType() {
			return type;
		}
		public ReadImage setType(String type) {
			this.type = type;
			return this;
		}
		public String getSuffix() {
			return suffix;
		}
		public ReadImage setSuffix(String suffix) {
			this.suffix = suffix;
			return this;
		}
	}

}
